import os,datetime

from utils.DBService import sqlquery

SHEBEI_SQL = """SELECT
    subquery.departname,q.banhezhanminchen,
    SUM(CASE WHEN {} THEN 1 ELSE 0 END) AS count_yawan,
	SUM(CASE WHEN q.state = 1 THEN 1 ELSE 0 END) AS count_1,
    SUM(CASE WHEN q.state = 2 THEN 1 ELSE 0 END) AS count_2,
    SUM(CASE WHEN q.state = 3 THEN 1 ELSE 0 END) AS count_3,
    SUM(CASE WHEN q.state = 4 THEN 1 ELSE 0 END) AS count_4
FROM
    syszhuangtaiView AS q
JOIN
    (
        SELECT
            b.gprsbianhao,
            c.departname
        FROM
            t_s_depart AS c
        JOIN
            Banhezhanxinxi AS b ON c.id = b.departid
        WHERE
            b.orgcode LIKE (
                SELECT org_code FROM t_s_depart WHERE id = '{}'
            ) + '%'
            AND b.gprstype NOT IN (1, 2, 5)
    ) AS subquery ON q.gprsbianhao = subquery.gprsbianhao
WHERE
    {}
    AND {}
GROUP BY
    subquery.departname,q.banhezhanminchen;"""

def yawan_ovStatic(Args,user):
    print('Output参数:\n\t', Args)
    depId = Args['depId']
    dates = Args['dates']
    depId = '\',\''.join(depId.split(','))

    if depId == '8a8ab0b246dc81120146dc8180ba0017'or \
            Args['depId'] == 'All':
        depId = '8a8ab0b246dc81120146dc8180ba0017'
    elif depId == 'f6323b406b4fab1b016bacabdb9f01d3':
        depId = 'f6323b406b4fab1b016bacabdb9f01d3'
    elif depId == 'f6323b406b4fab1b016bacac674901d5':
        depId = 'f6323b406b4fab1b016bacac674901d5'
    elif depId == 'f6323b406d19fc87016d1a5c43910016':
        depId = 'f6323b406d19fc87016d1a5c43910016'
    elif depId == 'f6323b406d19fc87016d1a5b861b0014':
        depId = 'f6323b406d19fc87016d1a5b861b0014'
    else:
        depId = '8a8ab0b246dc81120146dc8180ba0017'

    dateWhere = 'CONVERT(nvarchar(10), q.SYRQ, 120)in (\'{}\')'
    datearray = dates.split(',')
    if len(datearray) == 2:
        dateWhere = 'CONVERT(nvarchar(10), q.SYRQ, 120)>=\'{}\' and CONVERT(nvarchar(10), q.SYRQ, 120)<=\'{}\''.format(
            datearray[0], datearray[1])
    else:
        dateWhere = dateWhere.format('\',\''.join(dates.split(',')))
    yali = 'q.gprstype = 4'
    wanneng = 'q.gprstype = 3'
    banhezhanyaliSql = SHEBEI_SQL.format(yali,depId,dateWhere,yali)
    shebeiyData = sqlquery(SHEBEI_SQL.format(yali,depId,dateWhere,yali))
    print('SQLy:\n\t', banhezhanyaliSql)

    banhezhanwannengSql = SHEBEI_SQL.format(wanneng,depId,dateWhere,wanneng)
    print('SQLw:\n\t', banhezhanwannengSql)
    shebeiwData = sqlquery(SHEBEI_SQL.format(wanneng,depId,dateWhere,wanneng))

    slData = []
    slData += [process_data(data) for data in shebeiyData]
    slData += [process_data(data) for data in shebeiwData]
    print('Data:\n\t', slData)

    resdata = []
    if slData.__len__() == 0:
        marktitle = '当前时间没有运行的设备'
        return resdata,slData,marktitle
    else:
        if len(datearray) == 2:
            start = datetime.datetime.strptime(datearray[0], '%Y-%m-%d')
            end = datetime.datetime.strptime(datearray[1], '%Y-%m-%d')
            marktitle = str(start.year) + '年' + str(start.month) + '月' + str(start.day) + '日' + '到' + str(
                end.year) + '年' + str(end.month) + '月' + str(end.day) + '日' + '试验设备情况如下'
        else:
            start = datetime.datetime.strptime(datearray[0], '%Y-%m-%d')
            marktitle = str(start.year) + '年' + str(start.month) + '月' + str(start.day) + '日' + '试验设备情况如下:'

    a_list = []
    b_list = []
    c_list = []
    d_list = []
    children = []
    for item in slData:
        station_name = item[0]
        a = item[0:]
        data = {
            'key': slData.index(item),
            'departname': a[0],
            "yawan":a[1],
            'cjpanshu': a[2],
            'cjichaobL': a[3],
            'zjpanshu': a[4],
            'zjichaobL': a[5],
            'gjpanshu': a[6]
        }
        table_head = [
            {
                "title": '设备名称',
                "dataIndex": list(data.keys())[2],
                "key": list(data.keys())[2],
            },
            {
                "title": '设备总数',
                "dataIndex": list(data.keys())[3],
                "key": list(data.keys())[3],
            },
            {
                "title": '正常工作',
                "dataIndex": list(data.keys())[4],
                "key": list(data.keys())[4],
            },
            {
                "title": '延时工作',
                "dataIndex": list(data.keys())[5],
                "key": list(data.keys())[5],
            },
            {
                "title": '未工作',
                "dataIndex": list(data.keys())[6],
                "key": list(data.keys())[6],
            },
            {
                "title": '未安装',
                "dataIndex": list(data.keys())[7],
                "key": list(data.keys())[7],
            }]
        if station_name.startswith("武黄1标"):
            a_list.append(data)
        elif station_name.startswith("武黄2标") :
            b_list.append(data)
        elif station_name.startswith("武黄3标") :
            c_list.append(data)
        else:
            d_list.append(data)
    if len(a_list)>0:
        add_totals(a_list)
        childrenA_Data = {"children":[{"title": '武黄1标试验室', "data": {"tableHead": table_head, "tableBody": a_list}}] ,"title": '武黄1标'}
        children.append(childrenA_Data)
    if len(b_list)>0:
        add_totals(b_list)
        childrenB_Data = {"children":[{"title": '武黄2标试验室', "data": {"tableHead": table_head, "tableBody": b_list}}] ,"title": '武黄2标'}
        children.append(childrenB_Data)
    if len(c_list)>0:
        add_totals(c_list)
        childrenB_Data = {"children":[{"title": '武黄3标试验室', "data": {"tableHead": table_head, "tableBody": c_list}}] ,"title": '武黄3标'}
        children.append(childrenB_Data)
    if len(d_list)>0:
        add_totals(d_list)
        childrenB_Data = {"children":[{"title": '武黄高速监理', "data": {"tableHead": table_head, "tableBody": d_list}}] ,"title": '武黄高速监理'}
        children.append(childrenB_Data)

    result = [{"title": "武黄高速", "children": children}]

    print("result:\n\t",result)
    return slData,result,marktitle;

def add_totals(data):
    # 计算合计值
    total_cjichaobL = sum(item["cjichaobL"] for item in data)
    total_zjpanshu = sum(item["zjpanshu"] for item in data)
    total_cjpanshu = sum(item["cjpanshu"] for item in data)
    total_zjichaobL = sum(item["zjichaobL"] for item in data)
    total_gjpanshu = sum(item["gjpanshu"] for item in data)

    # 添加合计项
    total_item = {
        "cjichaobL": total_cjichaobL,
        "cjpanshu": total_cjpanshu,
        "departname": "",
        "yawan":'合计',
        "key": "",
        "zjpanshu": total_zjpanshu,
        "zjichaobL":total_zjichaobL,
        "gjpanshu":total_gjpanshu
    }

    data.append(total_item)

    return data


def process_data(data):
    if os.environ.get("ENV_NAME") == '1':
        return [data[0], data[1], data[2] if data[2] is not None else 0.0,
                data[3] if data[3] is not None else 0.0, data[4] if data[4] is not None else 0.0,
                data[5] if data[5] is not None else 0.0,data[6] if data[6] is not None else 0.0]
    else:
        return [data[0],data[1].encode('latin1').decode('gbk'),
                data[2] if data[2] is not None else 0.0, data[3] if data[3] is not None else 0.0,
                data[4] if data[4] is not None else 0.0,data[5] if data[5] is not None else 0.0,
                data[6] if data[6] is not None else 0.0]

functionConf = {
    "name": "yawan_ovStatic",
    "description": "key point:试验室设备(万能机,压力机)。Check the status of the test equipment by ID and time",
    "parameters": {
        "type": "object",
        "properties": {
            "depId": {
                "type": "string",
                "description": "Department IDs.Department IDs.默认ID是:8a8ab0b246dc81120146dc8180ba0017. 武黄1标是(f6323b406b4fab1b016bacabdb9f01d3).武黄2标是(f6323b406b4fab1b016bacac674901d5)."
            },
            "dates": {
                "type": "string",
                "description": "Date(s) of material production in yyyy-MM-dd format. Use comma-separated values (e.g., '2023-07-14,2023-07-15')."
            }
        },
        "required": [
            "depId",
            "dates"
        ]
    }
}
